/**
 * Copyright 2011 Google Inc.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      http://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */
package com.google.api.adwords.starterkit.db.tables;

import com.google.api.adwords.starterkit.db.keys.PlatformKey;
import com.google.api.adwords.starterkit.db.structures.PlatformStructure;
import com.google.api.adwords.starterkit.utils.DumpExceptions;

import org.apache.log4j.Logger;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.HashMap;
import java.util.HashSet;

/**
 * The Class PlatformTargetTable encapsulates most of the access to  
 * PlatformTarget table in DB.
 * 
 * @author rodrigo.f.vale@gmail.com  (Rodrigo Vale)
 */
public class PlatformTargetTable {
  private PreparedStatement prepReplace;
  private PreparedStatement prepLoad;
  private PreparedStatement prepDel;
  private static final Logger log = Logger.getLogger("StarterKit");
  private Connection conn;
  

  /**
   * Instantiates a new PlatformTargetTable
   * 
   * @param conn the connection to local DB
   */
  public PlatformTargetTable(Connection conn) {
    this.conn = conn;
    try { 
      Statement stat = conn.createStatement();      
      stat.executeUpdate(
          "CREATE TABLE IF NOT EXISTS PlatformTargets "
        + "  ( "
        + "     LocalCampaignId INTEGER, "
        + "     CampaignId      LONG, "
        + "     Platform        STRING DEFAULT '',"
        + "     PRIMARY KEY (LocalCampaignId, Platform)"
        + "  );");
      
      stat.executeUpdate(
          "CREATE INDEX IF NOT EXISTS PlatformTargetsCampaignIdIndex " 
        + "on PlatformTargets (CampaignId);");            
      
      stat.executeUpdate(
          "CREATE INDEX " 
        + "IF NOT EXISTS PlatformTargetsLocalCampaignIdCampaignIdIndex " 
        + "on PlatformTargets (LocalCampaignId, CampaignId);");
      
      prepLoad = conn.prepareStatement(
          "SELECT * FROM PlatformTargets WHERE LocalCampaignId = ?;"); 
      
      prepReplace = conn.prepareStatement(
          "REPLACE INTO PlatformTargets (LocalCampaignId, CampaignId, Platform" 
        + ") VALUES (?, ?, ?);");
      
      prepDel = conn.prepareStatement(
          "DELETE FROM PlatformTargets WHERE LocalCampaignId = ? " 
        + "and Platform = ?;");              
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }    
  }

  /**
   * Load a set of PlatformTarget from DB.
   *
   * @param platformKey the key that specifies the local campaign id
   * @return a set of platformTarget assigned to the local campaign id
   */
  public HashSet<PlatformStructure> get(PlatformKey platformKey) {
    HashSet<PlatformStructure> platforms = new HashSet<PlatformStructure>();
    ResultSet rs = null;
    try {
      prepLoad.setInt(1, platformKey.getLocalCampaignId());
      rs = prepLoad.executeQuery();
      if (rs.next()) {
        PlatformStructure platformStructure = getResultSet(rs);
        platforms.add(platformStructure);
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    } 
    return platforms;
  }

  /**
   * Gets the PlatformStructure from a generic SQL request.
   *
   * @param rs the ResultSet of a generic SQL request
   * @return all fields managed by Campaign wrapper 
   */
  public PlatformStructure getResultSet(ResultSet rs) {
    PlatformStructure platformStructure = null;
    try {
      platformStructure = new PlatformStructure(rs.getInt("LocalCampaignId"), 
          rs.getLong("CampaignId"), rs.getString("Platform"));
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
    return platformStructure;
  }
  
  /**
   * Save a campaign Platform to DB
   *
   * @param platformStructure the Platform structure
   * @param updated flag to point that the Campaign has an update to AdWords
   *                StarterKit
   */
  public void save(PlatformStructure platformStructure) {
    try {      
      prepReplace.setInt(1, platformStructure.getLocalCampaignId());
      prepReplace.setLong(2, platformStructure.getCampaignId());
      prepReplace.setString(3, platformStructure.getPlatform());
      prepReplace.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }   
  }
  
  /**
   * Save a campaign Platform to DB
   *
   * @param platformStructure the Platform structure
   * @param updated flag to point that the Campaign has an update to AdWords
   *                StarterKit
   */
  public void saveBatch(PlatformStructure platformStructure) {
    try {      
      prepReplace.setInt(1, platformStructure.getLocalCampaignId());
      prepReplace.setLong(2, platformStructure.getCampaignId());
      prepReplace.setString(3, platformStructure.getPlatform());
      prepReplace.addBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }   
  }  
  
  public void executeBatch() {
    try {
      prepReplace.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }
  }  
  
  /**
   * Delete a campaign Platform from DB
   *
   * @param platformKey the Platform to be deleted 
   */
  public void del(PlatformKey platformKey) {
    try {      
      prepDel.setInt(1, platformKey.getLocalCampaignId());
      prepDel.setString(2, platformKey.getPlatform());
      prepDel.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    }   
  }
  
  /**
   * Update campaigns ids at Platform target table.
   */
  public void updateCampaignsIds() {
    log.debug("Updating CampaignId at PlatformTargets table");
    HashMap<Long, Integer> campaignsIdMap = getNewCampaignsIds();
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
          "UPDATE PlatformTargets SET CampaignId = ? " +
          "WHERE LocalCampaignId = ?");
      int count = 0;
      for (long campaignId : campaignsIdMap.keySet()) {
        int localCampaignId = campaignsIdMap.get(campaignId);
        prep.setLong(1, campaignId);
        prep.setInt(2, localCampaignId);
        prep.addBatch();
        count++;
        if (count == 100000) {
          count = 0;
          prep.executeBatch();
        }
      }    
      prep.executeBatch();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }       
  }

  /**
   * Gets the campaign ids map for new campaigns
   *
   * @return the campaign ids map
   */
  public HashMap<Long, Integer> getNewCampaignsIds() {
    log.debug("Loading Campaigns Ids needed by new PlatformTargets");
    HashMap<Long, Integer> campaignsIdsMap = new HashMap<Long, Integer>(); 
    ResultSet rs = null;
    try {
      Statement stat = conn.createStatement();
      rs = stat.executeQuery(
            "SELECT DISTINCT ca.localId, ca.CampaignId FROM Campaigns ca, "
          + "PlatformTargets pt WHERE pt.LocalCampaignId = Ca.LocalId AND "
          + "pt.CampaignId = 0;");
      while (rs.next()) {
        campaignsIdsMap.put(rs.getLong("CampaignId"), rs.getInt("localId"));
      }
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (rs != null) {
        try {
          rs.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }
    return campaignsIdsMap;
  }
  
  /**
   * Return all PlatformTarget modified during the last Starter Kit execution
   * 
   * @return the ResultSet that contains platformTarget modified 
   */
  public ResultSet getModified() {
    PreparedStatement prep = null;
    ResultSet rs = null;
    // Updates all campaigns with ADD operator
    try {
      prep = conn.prepareStatement(
            "SELECT pt.CampaignId CampaignId, pt.Platform Platform " 
          + "FROM PlatformTargets pt, campaigns ca " 
          + "WHERE ca.IsPlatformModified = 1 " 
          + "AND pt.LocalCampaignId = ca.localId AND pt.CampaignId <> 0;");
      rs = prep.executeQuery();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);      
    }
    return rs;
  }

  /**
   * Reset all campaigns status flags to its default values
   */
  public void resetStatus() {
    PreparedStatement prep = null;
    try {
      prep = conn.prepareStatement(
            "UPDATE Campaigns SET IsPlatformModified = 0;");
      prep.executeUpdate();
    } catch (SQLException e) {
      DumpExceptions.dumpSQLException(e);
    } finally {
      if (prep != null) {
        try {
          prep.close();
        } catch (SQLException e) {
          DumpExceptions.dumpSQLException(e);
        }
      }
    }     
  }  
}
